﻿/*********************************************
 创建者：  dmhai
 创建时间：2017-03-30
 功能说明：品类参数浮动值管理
 ********************************************/
using com.nm.ApiServer;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
using System.Web.Caching;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Helper;
using com.nm.library.Helper;
using george.Data;
using george.Helper;
using PlanAPI.model;
using NHibernateVertica;
using Vertica.Data.VerticaClient;
using com.nm.library;

namespace PlanAPI
{
    [Description("品类参数浮动值管理")]
    public class PLBasModelFloatHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// <summary>
        /// 获取指定商品层的浮动值
        /// </summary>
        /// <param name="materialProperty">商品层</param>
        /// <param name="modelId">品类</param>
        /// <param name="isAreaShop">是否区域到店的浮动值</param>
        /// <returns></returns>
        public decimal GetFloat(string materialProperty,string modelId, bool isAreaShop)
        {
            decimal d = 0;
            string field = isAreaShop ? "AreaShopFloat" : "MonthWeekFloat";

            var sql = $" select {field} from ngoods.ngoods_PL_Bas_ModelFloat " +
                      $" where ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' and MaterialProperty = '{materialProperty}' and ModelID = '{modelId}' ";
            //先读取缓存的值，没有再访问数据库
            var obj = HttpRuntime.Cache.Get(sql);
            if (obj != null)
            {
                return (decimal) obj;
            }

            VerticaCommand command = new VerticaCommand(sql);
            obj = Helper.ExecuteScalar(command);
            if (decimal.TryParse(string.Concat(obj), out d))
            {
                HttpRuntime.Cache.Insert(sql, d, null, DateTime.Now.AddMinutes(30), TimeSpan.Zero, CacheItemPriority.High, null);
            }
            return d;
        }

        /// <summary>
        /// 获取区-店浮动值列表
        /// </summary>
        /// <returns></returns>
        public List<PLBasModelFloatModel.AreaShop> GetAreaShopFloatList()
        {
            var sql = $"select MaterialProperty,ModelID,AreaShopFloat from ngoods.ngoods_PL_Bas_ModelFloat  where ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}'  ; ";

            //先读取缓存的值，没有再访问数据库
            var obj = HttpRuntime.Cache.Get(sql);
            if (obj != null)
            {
                return (List<PLBasModelFloatModel.AreaShop>)obj;
            }

            VerticaCommand command = new VerticaCommand(sql);
            DataTable dt = Helper.ExecuteDataTable(command);

            var list = ModelConvertHelper<PLBasModelFloatModel.AreaShop>.ConvertToModel(dt).ToList();
            if (list != null && list.Count > 0)
            {
                HttpRuntime.Cache.Insert(sql, list, null, DateTime.Now.AddMinutes(30), TimeSpan.Zero, CacheItemPriority.High, null);
            }
            return list;
        }

        /// <summary>
        /// 获取月-周浮动值列表
        /// </summary>
        /// <returns></returns>
        public List<PLBasModelFloatModel.MonthWeek> GetMonthWeekFloatList()
        {
            var sql = $"select MaterialProperty,ModelID,MonthWeekFloat from ngoods.ngoods_PL_Bas_ModelFloat  where ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}' ";

            //先读取缓存的值，没有再访问数据库
            var obj = HttpRuntime.Cache.Get(sql);
            if (obj != null)
            {
                return (List<PLBasModelFloatModel.MonthWeek>)obj;
            }

            VerticaCommand command = new VerticaCommand(sql);
            DataTable dt = Helper.ExecuteDataTable(command);
            var list = ModelConvertHelper<PLBasModelFloatModel.MonthWeek>.ConvertToModel(dt).ToList();
            if (list != null && list.Count > 0)
            {
                HttpRuntime.Cache.Insert(sql, list, null, DateTime.Now.AddMinutes(30), TimeSpan.Zero, CacheItemPriority.High, null);
            }
            return list;
        }

        /// Writer：梁家健
        /// Create Date：2017-04-21
        [MustLogin]
        [Description("品类参数浮动值列表")]
        [ReturnType(typeof(IList<PLBasModelFloatModel.ListModel>))]
        public ApiResult GetPLBasModelFloatList([ParamDescription("商品层")] string MaterialProperty = "",
            [ParamDescription("品类")] string ModelID = "")
        {
            try
            {
                var dt = ReturnPLBasModelFloatList(MaterialProperty, ModelID);
                var list = ModelConvertHelper<PLBasModelFloatModel.ListModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// <summary>
        /// 返回品类参数浮动值
        /// </summary>
        /// <param name="MaterialProperty"></param>
        /// <param name="ModelID"></param>
        /// <returns></returns>
        /// Writer：梁家健
        /// Create Date：2017-04-21
        public DataTable ReturnPLBasModelFloatList(string MaterialProperty, string ModelID)
        {
            try
            {
                var sql = $@"select mf.*,ms.ConName as ModelName from ngoods.ngoods_PL_Bas_ModelFloat mf 
inner join (
select ConName,ConID from ngoods.ngoods_PL_PlanMatConSet  where MatConFlag=4 and AllowUsed=0 and ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}') ms
on mf.ModelID=ms.ConID
where 1=1 and mf.ExtrSource='{UserInfo.UserExtrSource}' and mf.CompanyID='{UserInfo.UserCompanyId}'
{(string.IsNullOrEmpty(MaterialProperty) ? "" : $@" and mf.MaterialProperty='{MaterialProperty}' ")}
{(string.IsNullOrEmpty(ModelID) ? "" : $@" and mf.ModelID='{ModelID}' ")}
order by mf.MaterialProperty,mf.ModelID";
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        
        /// Writer：梁家健
        /// Create Date：2017-04-21
        [MustLogin]
        [Description("品类选择列表")]
        [ReturnType(typeof(IList<BaseInfoModel>))]
        public ApiResult GetSelectModelID(string MaterialProperty)
        {
            try
            {
                var sql = $@"select * from ngoods.ngoods_PL_PlanMatConSet ms 
left join(select (case ModelID when null then 0 else 1 end) as isExists,ModelID from ngoods.ngoods_PL_Bas_ModelFloat where MaterialProperty='{MaterialProperty}'  and ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}')mf on ms.ConID=mf.ModelID
where ms.MatConFlag=4 and ms.AllowUsed=0  and ms.ExtrSource='{UserInfo.UserExtrSource}' and ms.CompanyID='{UserInfo.UserCompanyId}'";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<BaseInfoModel>.ConvertToModel(dt);
                return Success(list);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-04-21
        [JsonType]
        [MustLogin]
        [Description("品类参数浮动值列表")]
        [ReturnType(typeof(int),"0-成功")]
        public ApiResult UpdatePLBasModelFloat([ParamDescription("商品层")] PLBasModelFloatModel.NeceModel param)
        {
            try
            {
                const string sql = @"UPDATE  ngoods.ngoods_PL_Bas_ModelFloat
SET     AreaShopFloat = {0} ,
        MonthWeekFloat = {1}
WHERE   MaterialProperty = '{2}' 
  and ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}'
        AND ModelID = '{3}'";
                if (param == null) return Error("参数对象为空");
                var cmdList = new List<CommandInfo>()
                {
                    new CommandInfo()
                    {
                        CommandText =
                            string.Format(sql, param.AreaShopFloat, param.MonthWeekFloat, param.MaterialProperty,
                                param.ModelID),
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：梁家健
        /// Create Date：2017-04-21
        [JsonType]
        [MustLogin]
        [Description("品类参数浮动值列表")]
        [ReturnType(typeof(int), "0-成功")]
        public ApiResult CreatePLBasModelFloat([ParamDescription("商品层")] PLBasModelFloatModel.NeceModel param)
        {
            try
            {
                const string sql = @"
INSERT  INTO ngoods.ngoods_PL_Bas_ModelFloat
        ( CompanyID ,
          MaterialProperty ,
          ModelID ,
          AreaShopFloat ,
          MonthWeekFloat,
          ExtrSource 
        )
VALUES  ( '{0}' ,
          '{1}' ,
          '{2}' ,
          {3} ,
          {4} ,
          '{5}'
        );";
                if (param == null) return Error("参数对象为空");
                var cmdList = new List<CommandInfo>()
                {
                    new CommandInfo()
                    {
                        CommandText =
                            string.Format(sql, $"{UserInfo.UserCompanyId}", param.MaterialProperty,
                                param.ModelID, param.AreaShopFloat, param.MonthWeekFloat, $"{UserInfo.UserCompanyId}"),
                    }
                };
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }
    }

}
